def strify_vals(vals: list[str]):
    return ", ".join([f"'{c}'" for c in vals])


def gen_insert(table_name: str, columns: list[str]):
    return f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES "


def gen_update(columns: list[str]):
    return (
        f"ON DUPLICATE KEY UPDATE {', '.join([f"{c} = VALUES({c})" for c in columns])};"
    )


def gen_sql(data_list, *, table_name):

    columns = ["name", "code", "description", "app", "category", "status", "is_menu", "icon", "sort"]

    # 插入或更新父级权限
    parent_sql = gen_insert(table_name, columns)
    parent_values = []

    # 插入或更新子权限
    child_sql = gen_insert(table_name, [*columns, "parent_id", "group_id"])
    child_values = []

    # 收集父级和子权限
    for item in data_list:
        app = item["app"]
        category = item["category"]
        name = item["name"]
        code = item["code"]
        status = item.get("status", 0)
        description = item["description"] if item.get(
            "description") else ""
        is_menu = "1" if item.get("is_menu") else "0"
        sort = item.get("sort")  if item.get("sort") else "0"
        icon = item.get("icon", "")

        _type = item["_type"]
        if _type == "app":
            parent_values.append(
                f"({strify_vals([name, code, description, app, category, status, is_menu, icon, sort])})"
            )
        else:
            child_values.append(
                f"({strify_vals([name, code, description, app, category, status, is_menu, icon, sort])}, (SELECT id FROM (SELECT id FROM {table_name} WHERE code = '{item['_parent_code']}') AS tmp),  (SELECT id FROM (SELECT id FROM {table_name} WHERE code = '{item['_parent_code']}') AS tmp))"
            )

    # 父级权限 SQL
    if parent_values:
        parent_sql += (
            ",\n".join(parent_values)
            + " \n"
            + gen_update(["name", "description", "app", "category", "status", "is_menu", "icon", "sort"])
            + "\n"
        )
    else:
        parent_sql = ""

    # 子权限 SQL
    if child_values:
        child_sql += (
            ",\n".join(child_values)
            + " \n"
            + gen_update(
                ["name", "description", "app", "category",
                    "status", "is_menu", "icon", "sort", "parent_id", "group_id"]
            )
        )
    else:
        child_sql = ""

    return parent_sql + child_sql
